package com.example.dingtalk.service.impl;

import com.aliyun.dingtalkworkflow_1_0.Client;
import com.aliyun.dingtalkworkflow_1_0.models.*;
import com.aliyun.tea.TeaException;
import com.aliyun.teautil.models.RuntimeOptions;
import com.dingtalk.api.DefaultDingTalkClient;
import com.dingtalk.api.DingTalkClient;
import com.dingtalk.api.request.OapiProcessWorkrecordTaskQueryRequest;
import com.dingtalk.api.response.OapiProcessWorkrecordTaskQueryResponse;

import com.example.dingtalk.entity.dto.ExcelDto;
import com.example.dingtalk.entity.po.DingoaAccountsProcess;
import com.example.dingtalk.entity.po.DingoaManagementProcess;
import com.example.dingtalk.entity.po.DingoaSalesProcess;
import com.example.dingtalk.service.*;
import com.example.dingtalk.util.RedisUtil;

import com.taobao.api.ApiException;
import lombok.extern.slf4j.Slf4j;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ClassPathResource;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.function.Function;
import java.util.stream.Collectors;

import static com.example.dingtalk.util.DingConstants.*;

/**
 * @Description :
 * @Author : Bruce Lee
 * @CreateTime : 2024/5/23
 */

@Slf4j
@Service
public class OaServiceImpl implements OaService {

//    @Value("${dingtalk.accessToken}")
//    private String accessKeyId;

    @Value("${dingtalk.appKey}")
    private String appKey;

    @Value("${dingtalk.appSecret}")
    private String accessKeySecret;

    @Resource
    private RedisUtil redisUtil;


    @Resource
    private IDingoaManagementProcessService managementService;
    @Resource
    private IDingoaAccountsProcessService accountsService;
    @Resource
    private IDingoaSalesProcessService salesService;

    /**
     * 创建钉钉客户端
     *
     * @return
     * @throws Exception
     */
    public static com.aliyun.dingtalkworkflow_1_0.Client createClient() throws Exception {
        com.aliyun.teaopenapi.models.Config config = new com.aliyun.teaopenapi.models.Config();
        config.protocol = "https";
        config.regionId = "central";
        return new com.aliyun.dingtalkworkflow_1_0.Client(config);
    }

    /**
     * 获取审批实例信息
     *
     * @throws Exception
     */
    @Override
    public GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResult getProcessInstance(String processInstanceId) throws Exception {
        Client client = createClient();
        GetProcessInstanceHeaders getProcessInstanceHeaders = new GetProcessInstanceHeaders();
        String token = getAccessTokenFromRedis();
        //System.out.println(token);
        getProcessInstanceHeaders.xAcsDingtalkAccessToken = token;
        GetProcessInstanceRequest getProcessInstanceRequest = new GetProcessInstanceRequest()
                .setProcessInstanceId(processInstanceId);
        try {
            GetProcessInstanceResponse instanceWithOptions = client.getProcessInstanceWithOptions(getProcessInstanceRequest, getProcessInstanceHeaders, new RuntimeOptions());
            GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResult result = instanceWithOptions.getBody().getResult();
            return result;
        } catch (TeaException err) {
            if (!com.aliyun.teautil.Common.empty(err.code) && !com.aliyun.teautil.Common.empty(err.message)) {
                // err 中含有 code 和 message 属性，可帮助开发定位问题
                System.out.println(err.message);
            }

        } catch (Exception _err) {
            TeaException err = new TeaException(_err.getMessage(), _err);
            if (!com.aliyun.teautil.Common.empty(err.code) && !com.aliyun.teautil.Common.empty(err.message)) {
                // err 中含有 code 和 message 属性，可帮助开发定位问题
                System.out.println(err.message);
            }

        }
        return null;

    }

    /**
     * 获取用户待审批列表
     *
     * @param token
     * @throws ApiException
     */
    @Override
    public OapiProcessWorkrecordTaskQueryResponse.PageResult getUserApprovalIns(String token, String userId,
                                                                                long offset, long cnt, long status) throws ApiException {
        DingTalkClient client = new DefaultDingTalkClient("https://oapi.dingtalk.com/topapi/process/workrecord/task/query");
        OapiProcessWorkrecordTaskQueryRequest req = new OapiProcessWorkrecordTaskQueryRequest();
        req.setUserid(userId);
        req.setOffset(offset);
        req.setCount(cnt);
        req.setStatus(status);
        OapiProcessWorkrecordTaskQueryResponse rsp = client.execute(req, token);
        OapiProcessWorkrecordTaskQueryResponse.PageResult result = rsp.getResult();
        return result;
    }

    /**
     * 获取所有待办审批流ID列表
     *
     * @param startTime
     * @param processCode
     * @param nextToken
     * @param maxResults
     * @param statuses
     * @return
     * @throws Exception
     */
    @Override
    public ListProcessInstanceIdsResponseBody.ListProcessInstanceIdsResponseBodyResult getProcessListByPage(Long startTime, String processCode, Long nextToken, Long maxResults, String statuses) throws Exception {
        Client client = createClient();
        ListProcessInstanceIdsHeaders listProcessInstanceIdsHeaders = new ListProcessInstanceIdsHeaders();
        listProcessInstanceIdsHeaders.xAcsDingtalkAccessToken = getAccessTokenFromRedis();
        ListProcessInstanceIdsRequest listProcessInstanceIdsRequest = new ListProcessInstanceIdsRequest()
                .setStatuses(java.util.Arrays.asList(
//                        "RUNNING"
                        statuses
                ))
                .setStartTime(startTime)
                .setProcessCode(processCode)
                .setNextToken(nextToken)
                .setMaxResults(maxResults);
        try {
            ListProcessInstanceIdsResponse listProcessInstanceIdsResponse = client.listProcessInstanceIdsWithOptions(listProcessInstanceIdsRequest, listProcessInstanceIdsHeaders, new RuntimeOptions());
            return listProcessInstanceIdsResponse.getBody().getResult();

        } catch (TeaException err) {
            if (!com.aliyun.teautil.Common.empty(err.code) && !com.aliyun.teautil.Common.empty(err.message)) {
                // err 中含有 code 和 message 属性，可帮助开发定位问题
                System.out.println(err.message);
            }

        } catch (Exception _err) {
            TeaException err = new TeaException(_err.getMessage(), _err);
            if (!com.aliyun.teautil.Common.empty(err.code) && !com.aliyun.teautil.Common.empty(err.message)) {
                // err 中含有 code 和 message 属性，可帮助开发定位问题
                System.out.println(err.message);
            }

        }
        return null;
    }


    /**
     * 处理已完成审批单
     */
    @Override
    public void processListByPage(String processCode) throws Exception {
        // 每页大小
        Long pageSize = 20L;
        // 待办审批流状态: RUNNING、TERMINATED、COMPLETED
//        String statuses = "RUNNING";
        String statuses = "COMPLETED";


        // 获取查询的起始时间，默认结束时间是函数调用时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        // 往前推3个月
        c.add(Calendar.MONTH, -1);
        Date time = c.getTime();

        // 获取审批模板code
        //PROC-266BED5D-AFBF-4E84-B41E-F9963CBB414D管理费用流程（深圳）
        //PROC-7DB8B375-158E-47B4-A9AA-12BA01B1020E销售平台费用流程（义乌）
        //PROC-DAA0997E-0F58-4534-9957-655C83BF10AB应付账款流程（义乌
        //PROC-D168C11F-5BC8-47C9-B146-0EF510DF3D1E管理费用流程（义乌）
        ListProcessInstanceIdsResponseBody.ListProcessInstanceIdsResponseBodyResult result = getProcessListByPage(time.getTime(), processCode, 0L, pageSize, statuses);

        if (result != null) {
            processInstanceList(result.getList());
            while (result.getNextToken() != null) {
                result = getProcessListByPage(time.getTime(), processCode, Long.valueOf(result.getNextToken()), pageSize, statuses);
                processInstanceList(result.getList());
            }
        }
    }

    /**
     * 处理已完成数据
     * @param processInstanceIds
     */

    public void processInstanceList(List<String> processInstanceIds) {
        if (processInstanceIds == null || processInstanceIds.isEmpty()) {
            System.out.println("No process instance IDs to process.");
            return;
        }
        // 查询所有相关记录并分类
        List<DingoaManagementProcess> managementProcesses = managementService.lambdaQuery()
                .in(DingoaManagementProcess::getInstanceId, processInstanceIds)
                .eq(DingoaManagementProcess::getIsComplete, "待审批")
                .list();
        List<DingoaAccountsProcess> accountsProcesses = accountsService.lambdaQuery()
                .in(DingoaAccountsProcess::getInstanceId, processInstanceIds)
                .eq(DingoaAccountsProcess::getIsComplete, "待审批")
                .list();
        List<DingoaSalesProcess> salesProcesses = salesService.lambdaQuery()
                .in(DingoaSalesProcess::getInstanceId, processInstanceIds)
                .eq(DingoaSalesProcess::getIsComplete, "待审批")
                .list();

        // 将查询结果存储到 Set 中以便快速查找
        Set<String> handledInstanceIds = new HashSet<>();
        managementProcesses.forEach(p -> handledInstanceIds.add(p.getInstanceId()));
        accountsProcesses.forEach(p -> handledInstanceIds.add(p.getInstanceId()));
        salesProcesses.forEach(p -> handledInstanceIds.add(p.getInstanceId()));

        // 处理查询到的记录
        managementProcesses.forEach(p -> updateProcessStatus(p, managementService));
        accountsProcesses.forEach(p -> updateProcessStatus(p, accountsService));
        salesProcesses.forEach(p -> updateProcessStatus(p, salesService));

        // 处理未查询到的记录
        processInstanceIds.stream()
                .filter(instanceId -> !handledInstanceIds.contains(instanceId))
                .forEach(instanceId -> {
                    try {
                        GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResult processInstance = getProcessInstance(instanceId);
                        if ("agree".equals(processInstance.getResult())) {
                            String title = processInstance.getTitle();
                            ExpenseProcessHandler handler = getHandler(title);
                            if (handler != null) {
                                // 在处理之前检查记录是否已存在
                                if (!recordExistsInAnyTable(instanceId)) {
                                    handler.handle(processInstance, true, instanceId);
                                }  //System.out.println("Record with instanceId " + instanceId + " already exists in one of the tables.");
                            }
                        }
                    } catch (Exception e) {
                        System.out.println(e.getMessage());
                    }
                });
    }

    private boolean recordExistsInAnyTable(String instanceId) {
        boolean existsInSales = salesService.lambdaQuery()
                .eq(DingoaSalesProcess::getInstanceId, instanceId)
                .exists();

        boolean existsInManagement = managementService.lambdaQuery()
                .eq(DingoaManagementProcess::getInstanceId, instanceId)
                .exists();

        boolean existsInAccounts = accountsService.lambdaQuery()
                .eq(DingoaAccountsProcess::getInstanceId, instanceId)
                .exists();

        return existsInSales || existsInManagement || existsInAccounts;
    }

    /**
     * 更新状态
     * @param process
     * @param managementService
     */
    private void updateProcessStatus(DingoaManagementProcess process, IDingoaManagementProcessService managementService) {
        try {
            // 从外部系统获取流程实例详细信息
            GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResult processInstance = getProcessInstance(process.getInstanceId());
            if (processInstance != null) {
                // 设置完成时间
                LocalDateTime localDate = LocalDateTime.parse(processInstance.getFinishTime(), DateTimeFormatter.ISO_DATE_TIME);
                Date finishTime = Date.from(localDate.atZone(ZoneId.of("Asia/Shanghai")).toInstant());
                process.setFinishTime(finishTime);
                // 更新状态和时间
                process.setIsComplete("已完成");
                process.setUpdateTime(new Date());
                managementService.updateById(process);
            }
        } catch (Exception e) {
            System.out.println("更新管理流程状态失败: " + e.getMessage());
        }
    }

    private void updateProcessStatus(DingoaAccountsProcess process, IDingoaAccountsProcessService accountsService) {
        try {
            // 从外部系统获取流程实例详细信息
            GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResult processInstance = getProcessInstance(process.getInstanceId());
            if (processInstance != null) {
                // 设置完成时间
                LocalDateTime localDate = LocalDateTime.parse(processInstance.getFinishTime(), DateTimeFormatter.ISO_DATE_TIME);
                Date finishTime = Date.from(localDate.atZone(ZoneId.of("Asia/Shanghai")).toInstant());
                process.setFinishTime(finishTime);
                // 更新状态和时间
                process.setIsComplete("已完成");
                process.setUpdateTime(new Date());
                accountsService.updateById(process);
            }

        } catch (Exception e) {
            System.out.println("更新管理流程状态失败: " + e.getMessage());
        }
    }

    private void updateProcessStatus(DingoaSalesProcess process, IDingoaSalesProcessService salesService) {
        try {
            // 从外部系统获取流程实例详细信息
            GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResult processInstance = getProcessInstance(process.getInstanceId());
            if (processInstance != null) {
                // 设置完成时间
                LocalDateTime localDate = LocalDateTime.parse(processInstance.getFinishTime(), DateTimeFormatter.ISO_DATE_TIME);
                Date finishTime = Date.from(localDate.atZone(ZoneId.of("Asia/Shanghai")).toInstant());
                process.setFinishTime(finishTime);
                // 更新状态和时间
                process.setIsComplete("已完成");
                process.setUpdateTime(new Date());
                salesService.updateById(process);
            }

        } catch (Exception e) {
            System.out.println("更新管理流程状态失败: " + e.getMessage());
        }
    }

    /**
     * 循环处理四个组件审批单
     */
    @Transactional
    //@Scheduled(cron = "0 0 1 * *")
    @Override
    public void loopProcessingApproval() throws Exception {
        //PROC-266BED5D-AFBF-4E84-B41E-F9963CBB414D管理费用流程（深圳）
        //PROC-7DB8B375-158E-47B4-A9AA-12BA01B1020E销售平台费用流程（义乌）
        //PROC-DAA0997E-0F58-4534-9957-655C83BF10AB应付账款流程（义乌
        //PROC-D168C11F-5BC8-47C9-B146-0EF510DF3D1E管理费用流程（义乌）
        List<String> list = new ArrayList<>();
        list.add("PROC-266BED5D-AFBF-4E84-B41E-F9963CBB414D");
        list.add("PROC-7DB8B375-158E-47B4-A9AA-12BA01B1020E");
        list.add("PROC-DAA0997E-0F58-4534-9957-655C83BF10AB");
        list.add("PROC-D168C11F-5BC8-47C9-B146-0EF510DF3D1E");
        for (String s : list) {
            processListByPage(s);
        }
    }

    /**
     * @return 获取用户待审批实例ids
     * @throws ApiException
     */
    @Override
    public List<String> getApprovalIds() throws ApiException {
        String token = this.getAccessTokenFromRedis();
        // 获取所有审批实例ID列表
        List<String> processInstanceIds = new ArrayList<>();

        String userId = "13335729131062710";
        // 每页大小
        Long cnt = 50L;

        //初始偏移量
        Long offset = 0L;

        //待审批
        Long status = 0L;
        OapiProcessWorkrecordTaskQueryResponse.PageResult result = this.getUserApprovalIns(token, userId, offset, cnt, status);

        if (result != null) {
            if (result.getList() != null) {
                result.getList().forEach(processInstanceId -> {
                    processInstanceIds.add(processInstanceId.getInstanceId());
                    //System.out.println(processInstanceId.getTitle() + processInstanceId.getInstanceId());
                });

                while (result.getHasMore()) {
                    offset += cnt;
                    result = this.getUserApprovalIns(token, userId, offset, cnt, status);
                    if (result != null && result.getList() != null) {
                        result.getList().forEach(processInstanceId -> {
                            processInstanceIds.add(processInstanceId.getInstanceId());
                            //System.out.println(processInstanceId.getTitle());
                        });
                    }
                }
            }
        }
        return processInstanceIds;
    }

    /**
     * 获取钉钉API的accessToken
     *
     * @return
     */
    @Override
    public String getAccessToken() throws Exception {
        com.aliyun.teaopenapi.models.Config config = new com.aliyun.teaopenapi.models.Config();
        config.protocol = "https";
        config.regionId = "central";
        com.aliyun.dingtalkoauth2_1_0.Client client = new com.aliyun.dingtalkoauth2_1_0.Client(config);
        com.aliyun.dingtalkoauth2_1_0.models.GetAccessTokenRequest getAccessTokenRequest = new com.aliyun.dingtalkoauth2_1_0.models.GetAccessTokenRequest()
                .setAppKey(appKey)
                .setAppSecret(accessKeySecret);
        try {
            return client.getAccessToken(getAccessTokenRequest).getBody().getAccessToken();
        } catch (TeaException err) {
            if (!com.aliyun.teautil.Common.empty(err.code) && !com.aliyun.teautil.Common.empty(err.message)) {
                // err 中含有 code 和 message 属性，可帮助开发定位问题
            }
        } catch (Exception _err) {
            TeaException err = new TeaException(_err.getMessage(), _err);
            if (!com.aliyun.teautil.Common.empty(err.code) && !com.aliyun.teautil.Common.empty(err.message)) {
                // err 中含有 code 和 message 属性，可帮助开发定位问题
            }
        }
        return null;
    }


    /**
     * 从redis中获取钉钉API的accessToken, 避免频繁调用钉钉接口
     */
    public String getAccessTokenFromRedis() {
        String accessToken = redisUtil.getCacheObject("accessToken");
        if (accessToken == null) {
            try {
                accessToken = getAccessToken();
                // 钉钉官方文档中accessToken的有效期为2小时
                redisUtil.setCacheObject("accessToken", accessToken, 59 * 60 * 2, TimeUnit.SECONDS);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return accessToken;
    }

    /**
     * 循环处理待审批数据
     *
     * @param
     */
    @Override
    //@Scheduled(cron = "0 55 14 * * ?")
    public void processApprovalIds() throws Exception {

        List<String> instanceIds = this.getApprovalIds();
        if (instanceIds == null || instanceIds.isEmpty()) {
            // 如果 instanceIds 为空，直接返回或者执行其他逻辑
            return;
        }
        // 2. 批量查询数据库获取所有相关的记录
        Set<String> existingInstanceIds = new HashSet<>();
        existingInstanceIds.addAll(
                managementService.lambdaQuery().in(DingoaManagementProcess::getInstanceId, instanceIds).list().stream()
                        .map(DingoaManagementProcess::getInstanceId)
                        .collect(Collectors.toSet())
        );
        existingInstanceIds.addAll(
                accountsService.lambdaQuery().in(DingoaAccountsProcess::getInstanceId, instanceIds).list().stream()
                        .map(DingoaAccountsProcess::getInstanceId)
                        .collect(Collectors.toSet())
        );
        existingInstanceIds.addAll(
                salesService.lambdaQuery().in(DingoaSalesProcess::getInstanceId, instanceIds).list().stream()
                        .map(DingoaSalesProcess::getInstanceId)
                        .collect(Collectors.toSet())
        );

        // 3. 处理不存在于数据库中的 instanceIds
        List<String> idsToProcess = instanceIds.stream()
                .filter(id -> !existingInstanceIds.contains(id))
                .collect(Collectors.toList());

        for (String instanceId : idsToProcess) {
            GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResult instance = this.getProcessInstance(instanceId);
            String title = instance.getTitle();
            ExpenseProcessHandler handler = getHandler(title);
            if (handler != null) {
                handler.handle(instance, false, instanceId);
            }
        }
    }

    /**
     * 根据时间范围是否导出过导出待审批数据
     * @param startDate
     * @param endDate
     * @param flag  true 全量导出（未审批）  false 导出增量（未审批的）
     * @return
     * @throws IOException
     */
    @Override
    public byte[] exportExpensesToExcel(String startDate, String endDate,Boolean flag) throws IOException {

        // 读取模板
        String templatePath = "templates/2024年资金日记账模板.xlsx";
        ClassPathResource classPathResource = new ClassPathResource(templatePath);
        InputStream is = classPathResource.getInputStream();
        Workbook workbook = new XSSFWorkbook(is);

        // 获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);

        // 修改标题行：将 "2024年8月资金日记账" 改为当前年月
        Row titleRow = sheet.getRow(0); // 假设标题在第一行
        if (titleRow != null) {
            Cell titleCell = titleRow.getCell(0); // 假设标题在 A1 单元格
            if (titleCell != null) {
                String currentYearMonth = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy年M月"));
                titleCell.setCellValue(currentYearMonth + "资金日记账");
            }
        }


        List<ExcelDto> allExpenses = new ArrayList<>();

        // 获取销售费用数据
        allExpenses.addAll(salesService.toExcelDto(startDate, endDate,flag));

        // 获取管理费用数据
        allExpenses.addAll(managementService.toExcelDto(startDate, endDate,flag));

        // 获取应付账款数据
        allExpenses.addAll(accountsService.toExcelDto(startDate, endDate,flag));

        // 填充数据
        fillInData(sheet, allExpenses);

        // 将Excel文件转换为字节数组
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    /**
     * 根据日期范围导出数据
     *
     * @param startDate
     * @param endDate
     * @return
     * @throws IOException
     */
    @Override
    public byte[] exportExcelIsComplete(String startDate, String endDate,Boolean flag) throws IOException {
        // 读取模板
        String templatePath = "templates/2024年资金日记账模板.xlsx";
        ClassPathResource classPathResource = new ClassPathResource(templatePath);
        InputStream is = classPathResource.getInputStream();
        Workbook workbook = new XSSFWorkbook(is);

        // 获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);

        // 修改标题行：将 "2024年8月资金日记账" 改为当前年月
        Row titleRow = sheet.getRow(0); // 假设标题在第一行
        if (titleRow != null) {
            Cell titleCell = titleRow.getCell(0); // 假设标题在 A1 单元格
            if (titleCell != null) {
                String currentYearMonth = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy年M月"));
                titleCell.setCellValue(currentYearMonth + "资金日记账");
            }
        }


        List<ExcelDto> allExpenses = new ArrayList<>();


        // 获取销售费用数据
        allExpenses.addAll(salesService.toExcel(startDate, endDate,flag));

        // 获取管理费用数据
        allExpenses.addAll(managementService.toExcel(startDate, endDate,flag));

        // 获取应付账款数据
        allExpenses.addAll(accountsService.toExcel(startDate, endDate,flag));

        // 填充数据
        fillInData(sheet, allExpenses);

        // 将Excel文件转换为字节数组
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    @Override
    public void refreshStatus() {
        // 查询所有相关记录并分类
        List<DingoaManagementProcess> managementProcesses = managementService.lambdaQuery()
                .eq(DingoaManagementProcess::getIsComplete, PENDING_APPROVAL)
                .list();
        List<DingoaAccountsProcess> accountsProcesses = accountsService.lambdaQuery()
                .eq(DingoaAccountsProcess::getIsComplete, PENDING_APPROVAL)
                .list();
        List<DingoaSalesProcess> salesProcesses = salesService.lambdaQuery()
                .eq(DingoaSalesProcess::getIsComplete, PENDING_APPROVAL)
                .list();

        // 将查询结果存储到 Set 中以便快速查找
        Set<String> handledInstanceIds = new HashSet<>();
        managementProcesses.forEach(p -> handledInstanceIds.add(p.getInstanceId()));
        accountsProcesses.forEach(p -> handledInstanceIds.add(p.getInstanceId()));
        salesProcesses.forEach(p -> handledInstanceIds.add(p.getInstanceId()));
        log.info("手动同步待审批的instanceId:{}",handledInstanceIds.size());
        // 处理查询到的记录
        managementProcesses.forEach(p -> checkProcessStatus(p,DingoaManagementProcess::getInstanceId));
        accountsProcesses.forEach(p -> checkProcessStatus(p,DingoaAccountsProcess::getInstanceId));
        salesProcesses.forEach(p -> checkProcessStatus(p,DingoaSalesProcess::getInstanceId));
    }

    /**
     * 检查并更新流程状态
     * @param process
     * @param getInstanceIdFunction
     * @param <T>
     */
    private <T> void checkProcessStatus(T process,  Function<T, String> getInstanceIdFunction) {
        try {
            String instanceId = getInstanceIdFunction.apply(process);
            // 从外部系统获取流程实例详细信息
            GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResult processInstance = getProcessInstance(instanceId);
            if (processInstance != null) {
                // 更新状态和时间
                if (process instanceof DingoaManagementProcess) {
                    DingoaManagementProcess managementProcess = (DingoaManagementProcess) process;
                    if (COMPLETE.equals(processInstance.getStatus())){
                        // 设置完成时间
                        LocalDateTime localDate = LocalDateTime.parse(processInstance.getFinishTime(), DateTimeFormatter.ISO_DATE_TIME);
                        Date finishTime = Date.from(localDate.atZone(ZoneId.of(ZONE)).toInstant());
                        managementProcess.setFinishTime(finishTime);
                        managementProcess.setIsComplete(COMPLETED);
                    }
                    if (instanceId.equals(managementProcess.getInstanceId())){
                        List<GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResultFormComponentValues> formComponentValues = processInstance.getFormComponentValues();
                        for (GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResultFormComponentValues componentValue : formComponentValues) {
                            if (BANK_ACCOUNT.equals(componentValue.getName())||BANK_ACCOUNT2.equals(componentValue.getName())){
                                managementProcess.setRecipientAccountNumber(componentValue.getValue());
                            }
                        }
                    }
                    managementProcess.setUpdateTime(new Date());
                    managementService.updateById(managementProcess);
                } else if (process instanceof DingoaAccountsProcess) {
                    DingoaAccountsProcess accountsProcess = (DingoaAccountsProcess) process;
                    if (COMPLETE.equals(processInstance.getStatus())){
                        // 设置完成时间
                        LocalDateTime localDate = LocalDateTime.parse(processInstance.getFinishTime(), DateTimeFormatter.ISO_DATE_TIME);
                        Date finishTime = Date.from(localDate.atZone(ZoneId.of(ZONE)).toInstant());
                        accountsProcess.setFinishTime(finishTime);
                        accountsProcess.setIsComplete(COMPLETED);
                    }

                    if (instanceId.equals(accountsProcess.getInstanceId())){
                        List<GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResultFormComponentValues> formComponentValues = processInstance.getFormComponentValues();
                        for (GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResultFormComponentValues componentValue : formComponentValues) {
                            if (BANK_ACCOUNT.equals(componentValue.getName())||BANK_ACCOUNT2.equals(componentValue.getName())){
                                accountsProcess.setRecipientAccountNumber(componentValue.getValue());
                            }
                        }
                    }
                    accountsProcess.setUpdateTime(new Date());
                    accountsService.updateById(accountsProcess);
                } else if (process instanceof DingoaSalesProcess) {
                    DingoaSalesProcess salesProcess = (DingoaSalesProcess) process;
                    if (COMPLETE.equals(processInstance.getStatus())){
                        // 设置完成时间
                        LocalDateTime localDate = LocalDateTime.parse(processInstance.getFinishTime(), DateTimeFormatter.ISO_DATE_TIME);
                        Date finishTime = Date.from(localDate.atZone(ZoneId.of(ZONE)).toInstant());
                        salesProcess.setFinishTime(finishTime);
                        salesProcess.setIsComplete(COMPLETED);
                    }
                    if (instanceId.equals(salesProcess.getInstanceId())){
                        List<GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResultFormComponentValues> formComponentValues = processInstance.getFormComponentValues();
                        for (GetProcessInstanceResponseBody.GetProcessInstanceResponseBodyResultFormComponentValues componentValue : formComponentValues) {
                            if (BANK_ACCOUNT.equals(componentValue.getName())||BANK_ACCOUNT2.equals(componentValue.getName())){
                                salesProcess.setRecipientAccountNumber(componentValue.getValue());
                            }
                        }
                    }
                    salesProcess.setUpdateTime(new Date());
                    salesService.updateById(salesProcess);
                }
            }
        } catch (Exception e) {
            log.error("更新管理流程状态失败: ", e);
        }
    }

    /**
     * 填充数据
     *
     * @param sheet
     * @param allExpenses
     */
    private void fillInData(Sheet sheet, List<ExcelDto> allExpenses) {
        int rowNum = 2;
        for (ExcelDto expense : allExpenses) {
            Row row = sheet.createRow(rowNum++);
            //记账公司
            row.createCell(1).setCellValue(expense.getPaymentCompany());
            //记账部门
            row.createCell(2).setCellValue(expense.getApplicantDepartment());
            //申请人部门
            row.createCell(3).setCellValue(expense.getApplicantDepartment());
            row.createCell(4).setCellValue(expense.getProject());
            row.createCell(5).setCellValue(expense.getApplicant());
            row.createCell(6).setCellValue(expense.getRecipientName());
            row.createCell(7).setCellValue(expense.getReceivingBank());

            //身份证号
            row.createCell(8).setCellValue(expense.getRecipientIdNumber());
            //收款单位（个人）账号
            row.createCell(9).setCellValue(expense.getRecipientAccountNumber());
            row.createCell(10).setCellValue(expense.getPaymentBank());

            row.createCell(11).setCellValue(expense.getDebitSubject());
            row.createCell(13).setCellValue(expense.getAttributionDetails());
            row.createCell(14).setCellValue(expense.getSummary());
            row.createCell(15).setCellValue(expense.getCurrency());

            row.createCell(17).setCellValue(expense.getCredit().toString());
            row.createCell(19).setCellValue(expense.getVoucherColumn());

        }
    }

    /**
     * 根据title获取handler
     *
     * @param title
     * @return
     */
    public ExpenseProcessHandler getHandler(String title) {
        if (title.contains("管理费用")) {
            return (ExpenseProcessHandler) managementService;
        } else if (title.contains("应付账款")) {
            return (ExpenseProcessHandler) accountsService;
        } else if (title.contains("销售平台")) {
            return (ExpenseProcessHandler) salesService;
        }
        return null;
    }
}
